#!/usr/bin/env python3
import configparser
from datetime import datetime
# 表配置
from pathlib import Path

from auto.untils import MySQLCursor

TABLES_CONFIG = [
    {'name': 'sys_baidu_city', 'where': '1=1', 'limit': 1000},
    {'name': 'tb_big_screen_menu', 'where': 'user_id = 0'},
    {'name': 'tb_warehouse_in_out_reason', 'where': '1=1'},
    {'name': 'tb_month_statistic_score', 'where': '1=1'},
    {'name': 'tb_disaster', 'where': '1=1'},
    {'name': 'tb_category_type', 'where': '1=1'},
    {'name': 'tb_dept_type', 'where': '1=1'},
    {'name': 'tb_rule', 'where': '1=1'},
    {'name': 'tb_smart_warehouse_big_screen_menu', 'where': '1=1'},
    {'name': 'tb_smart_warehouse_big_screen_menu_link', 'where': '1=1'},
    # 添加更多表...
]


def export_data(database):
    config = configparser.ConfigParser()
    config.read("./config/config.ini", encoding='utf-8')
    db = MySQLCursor(
        host=config['source']['host'],
        port=int(config['source']['port']),
        user=config['source']['user'],
        password=config['source']['password'],
        database=database
    )

    with db.get_connection() as conn:
        with conn.cursor() as cursor:
            file_path = Path(r"./data/export_equipment.sql")
            if file_path.exists():
                try:
                    file_path.unlink()
                    print("删除已经存在的 ==》 export_equipment.sql")
                except Exception as e:
                    print("删除 export_equipment.sql 失败".format(e))
                    exit(1)

            with open(file_path, 'w', encoding='utf-8') as f:
                f.write(f"-- 导出时间: {datetime.now()}\n")

                for table in TABLES_CONFIG:
                    sql = f"SELECT * FROM `{table['name']}`"
                    if 'where' in table:
                        sql += f" WHERE {table['where']}"
                    if 'limit' in table:
                        sql += f" LIMIT {table['limit']}"

                    cursor.execute(sql)
                    rows = cursor.fetchall()

                    if rows:
                        # 获取所有列名
                        all_columns = [col[0] for col in cursor.description]
                        # 过滤掉id字段
                        columns = [col for col in all_columns if col != 'id']

                        for row in rows:
                            values = []
                            # 创建一个字典，将列名与值对应
                            row_dict = dict(zip(all_columns, row))

                            # 只处理非id字段的值
                            for col in columns:
                                value = row_dict[col]
                                if value is None:
                                    values.append('NULL')
                                elif isinstance(value, (int, float)):
                                    values.append(str(value))
                                else:
                                    values.append(f"'{conn.escape_string(str(value))}'")

                            insert_sql = f"INSERT INTO `{database}`.`{table['name']}` ({', '.join([f'`{col}`' for col in columns])}) VALUES ({', '.join(values)});\n"
                            f.write(insert_sql)

                    print(f"表 {table['name']} 导出完成 (已排除id字段)")

        print("所有数据导出完成!")


if __name__ == "__main__":
    export_data("tt")
